package database_handler;

import com.sun.rowset.CachedRowSetImpl;
import help_util.DateTime;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;
import javax.swing.JApplet;
import model_controller.ControlLayer;
import sudoku_properties.BasicSudokuParam;

/*********************************************************************
 * Class DatabaseManager
 * 1. control the database related functions
 *********************************************************************/

public class DatabaseManager {

    public DatabaseManager(ControlLayer inputController){
        className = "com.mysql.jdbc.Driver";
        connectionURL = "jdbc:mysql://144.214.121.25:3306/websudoku?" + "user=root&password=nbuser";
        //con = null;
        //stmt = null;
        //rs = null;
        Controller = inputController;
        webSudoku = Controller.getWebSudoku();
    }

     /**
	 * Get a connection to the servlet.
	 */
	private URLConnection getSelectServletConnection()
		throws MalformedURLException, IOException {


		URL urlServlet = new URL(new URL("http://144.214.121.25:8080/SudokuITSWebsite/"), "SelectAppletData");
		URLConnection URLcon = urlServlet.openConnection();
		URLcon.setDoInput(true);
		URLcon.setDoOutput(true);
		URLcon.setUseCaches(false);
		URLcon.setRequestProperty(
			"Content-Type",
			"application/x-java-serialized-object");

		return URLcon;
	}
    private  CachedRowSet selectDataFromServlet(String sql) throws SQLException{
        CachedRowSet rowset = new CachedRowSetImpl();

        try{
            /* getDB result here*/

            // send data to the servlet
            URLConnection URLcon = getSelectServletConnection();
            OutputStream outstream = URLcon.getOutputStream();
            ObjectOutputStream oos = new ObjectOutputStream(outstream);
            oos.writeObject(sql);
            oos.flush();
            oos.close();

            InputStream instr = URLcon.getInputStream();
            ObjectInputStream inputFromServlet = new ObjectInputStream(instr);
            rowset = (CachedRowSet)inputFromServlet.readObject();

            inputFromServlet.close();
            instr.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}

        return rowset;
    }

    private URLConnection getUpdateServletConnection()
		throws MalformedURLException, IOException {

		URL urlServlet = new URL(new URL("http://144.214.121.25:8080/SudokuITSWebsite/"), "UpdateAppletData");
        URLConnection URLcon = urlServlet.openConnection();
		URLcon.setDoInput(true);
		URLcon.setDoOutput(true);
		URLcon.setUseCaches(false);
		URLcon.setRequestProperty(
			"Content-Type",
			"application/x-java-serialized-object");

		return URLcon;
	}
    private int updateDataFromServlet(String sql) throws SQLException{
        int isUpdate = 0;

        try{
            /* getDB result here*/

            // send data to the servlet
            URLConnection URLcon = this.getUpdateServletConnection();
            OutputStream outstream = URLcon.getOutputStream();
            ObjectOutputStream oos = new ObjectOutputStream(outstream);
            oos.writeObject(sql);
            oos.flush();
            oos.close();

            InputStream instr = URLcon.getInputStream();
            ObjectInputStream inputFromServlet = new ObjectInputStream(instr);
            isUpdate = Integer.parseInt((String)inputFromServlet.readObject());

            inputFromServlet.close();
            instr.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}

        return isUpdate;
    }
    /* Database Connection */
    /*
    public void connectDB() {
        try {
            Class.forName(className).newInstance();
            con = DriverManager.getConnection(connectionURL);
            stmt = con.createStatement();
        } catch (InstantiationException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }*/

    /* Close Database Connection */
    /*public void closeDB() {
        try {
            stmt.close();
            con.close();
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }*/

    /* Perform sql */
    // 20091016 phrase2 start
    public int getStepID(int gridID){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_SOLVING_STEPS = DatabaseParam.getTableGameSolvingSteps();
        final String[] TABLE_GAME_SOLVING_STEPS_PARAM = DatabaseParam.getTableGameSolvingStepsParam();
        int stepID = 0;

        dataString.append(" SELECT ");
        dataString.append(TABLE_GAME_SOLVING_STEPS_PARAM[2]);
        dataString.append(" FROM ");
        dataString.append(TABLE_GAME_SOLVING_STEPS);
        dataString.append(" WHERE ");
        /* check userID, puzzleID and gridID - hardcode here */
        dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[0] + "` ");
        dataString.append(" = '"+Controller.getUserID()+"' ");
        dataString.append(" AND ");
        dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[1] + "` ");
        dataString.append(" = '"+Controller.getPuzzleID()+"' ");
        dataString.append(" AND ");
        dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[3] + "` ");
        dataString.append(" = '"+ gridID +"' ");
        dataString.append(" ORDER BY timestamp DESC");


        try {
            CachedRowSet rowset = this.selectDataFromServlet(dataString.toString());
            //rs = stmt.executeQuery(dataString.toString());
            if(rowset .next()){
            //if(rs.first()){ /* if there is elemenet in result set */
                stepID = rs.getInt(TABLE_GAME_SOLVING_STEPS_PARAM[2]);
            //}
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return stepID;
    }
    /* Return Boolean */
    public boolean addGameSolvingSteps(int stepID, int gridID, int gridValue, int[] hintsUsed, int SGused, int NSused, int NPused, String elapsedTime){

        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_SOLVING_STEPS = DatabaseParam.getTableGameSolvingSteps();
        final String[] TABLE_GAME_SOLVING_STEPS_PARAM = DatabaseParam.getTableGameSolvingStepsParam();
        boolean isAdded = false;

        dataString.append(" INSERT INTO websudoku.");
        dataString.append(TABLE_GAME_SOLVING_STEPS);
        dataString.append(" ( ");
        for(int i=0; i<TABLE_GAME_SOLVING_STEPS_PARAM.length; i++){
            dataString.append(" `");
            dataString.append(TABLE_GAME_SOLVING_STEPS_PARAM[i].toString());
            dataString.append("` ");
            if((TABLE_GAME_SOLVING_STEPS_PARAM.length-1)!=i){ /* not last param */
                dataString.append(",");
            }
        }
        dataString.append(" ) VALUES ( ");
        dataString.append(" '"+Integer.toString(Controller.getUserID())+"', ");
        dataString.append(" '"+Integer.toString(Controller.getPuzzleID())+"', ");
        dataString.append(" '"+Integer.toString(stepID)+"', ");
        dataString.append(" '"+Integer.toString(gridID)+"', ");
        dataString.append(" '"+Integer.toString(gridValue)+"', ");
        dataString.append(" '"+Integer.toString(hintsUsed[0])+"', ");
        dataString.append(" '"+Integer.toString(hintsUsed[1])+"', ");
        dataString.append(" '"+Integer.toString(hintsUsed[2])+"', ");
        dataString.append(" '"+Integer.toString(hintsUsed[3])+"', ");
        dataString.append(" '"+Integer.toString(SGused)+"', ");
        dataString.append(" '"+Integer.toString(NSused)+"', ");
        dataString.append(" '"+Integer.toString(NPused)+"', ");
        dataString.append(" '"+elapsedTime+"', ");
        dataString.append(" '"+DateTime.getTimestamp()+"' ");
        dataString.append(" ) ");

        try{
            //isAdded =(stmt.executeUpdate(dataString.toString())> 0);
            isAdded = (this.updateDataFromServlet(dataString.toString())>0);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return isAdded;
    }
    /* return Boolean */
    public boolean removeGameSolvingSteps(int inputRemoveStep){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_SOLVING_STEPS = DatabaseParam.getTableGameSolvingSteps();
        final String[] TABLE_GAME_SOLVING_STEPS_PARAM = DatabaseParam.getTableGameSolvingStepsParam();
        boolean isRemoved = false;
        boolean isUpdated = false;

        /* if input remove step == 0, then remove the last added step*/
        int removeStep = inputRemoveStep;
        /* Step 1: remove the record */
        /* Step 1a: get the max step number - mysql cannot execute delete statement with select query */
        if(removeStep==0){
            /* if max, then no need to update, thus */
            isUpdated = true;
            dataString.append(" SELECT MAX( " );
            dataString.append(TABLE_GAME_SOLVING_STEPS_PARAM[2]);
            dataString.append( " ) FROM ");
            dataString.append(TABLE_GAME_SOLVING_STEPS);
            dataString.append(" WHERE ");
            dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[0] + "` ");
            dataString.append(" = '"+Controller.getUserID()+"' ");
            dataString.append(" AND ");
            dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[1] + "` ");
            dataString.append(" = '"+Controller.getPuzzleID()+"' ");
            dataString.append(" ORDER BY timestamp DESC");
            try {
                //rs = stmt.executeQuery(dataString.toString());
                CachedRowSet rowset = this.selectDataFromServlet(dataString.toString());
                if(rowset.next()){
                    removeStep = rowset.getInt(1);
                }
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        /* Step 1b: delete the record with max step */
        dataString = new StringBuffer();
        dataString.append(" DELETE FROM websudoku.");
        dataString.append(TABLE_GAME_SOLVING_STEPS);
        dataString.append(" WHERE ");
        dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[0] + "` ");
        dataString.append(" = '"+Controller.getUserID()+"' ");
        dataString.append(" AND ");
        dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[1] + "` ");
        dataString.append(" = '"+Controller.getPuzzleID()+"' ");
        dataString.append(" AND ");
        dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[2] + "` ");
        dataString.append(" = '"+removeStep+"' ");

        try {
            //isRemoved = stmt.executeUpdate(dataString.toString())>0 ;
            isRemoved = (this.updateDataFromServlet(dataString.toString()) > 0);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        /* Step 2: update the affected rows or records */
        /* add some update here...*/
        if( removeStep != 0 ){
            dataString = new StringBuffer();
            dataString.append(" UPDATE websudoku.");
            dataString.append(TABLE_GAME_SOLVING_STEPS);
            dataString.append(" SET ");
            dataString.append(TABLE_GAME_SOLVING_STEPS_PARAM[2]
                    +" = "+ TABLE_GAME_SOLVING_STEPS_PARAM[2]+ "- 1 ");
            dataString.append(" WHERE ");
            dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[0] + "` ");
            dataString.append(" = '"+Controller.getUserID()+"' ");
            dataString.append(" AND ");
            dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[1] + "` ");
            dataString.append(" = '"+Controller.getPuzzleID()+"' ");
            dataString.append(" AND ");
            dataString.append(" `" + TABLE_GAME_SOLVING_STEPS_PARAM[2] + "` ");
            dataString.append(" > '"+removeStep+"' ");
        }
        try {
            //isUpdated = stmt.executeUpdate(dataString.toString())>=0 ;
            isUpdated = (this.updateDataFromServlet(dataString.toString()) > 0);
            // as maybe update Zero row
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return isRemoved && isUpdated;
    }

    // 20091029 phrase2 start
    public boolean addStepsInStepsMatrix(int stepID, String depthSteps, String breadthSteps){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_STEPS_MATRIX = DatabaseParam.getTableGameStepsMatrix();
        final String[] TABLE_GAME_STEPS_MATRIX_PARAM = DatabaseParam.getTableGameStepsMatrixParam();
        boolean isAdded= false;

        dataString.append(" INSERT INTO websudoku.");
        dataString.append(TABLE_GAME_STEPS_MATRIX);
        dataString.append(" ( ");
        for(int i=0; i<TABLE_GAME_STEPS_MATRIX_PARAM.length; i++){
            dataString.append(" `");
            dataString.append(TABLE_GAME_STEPS_MATRIX_PARAM[i].toString());
            dataString.append("` ");
            if((TABLE_GAME_STEPS_MATRIX_PARAM.length-1)!=i){ /* not last param */
                dataString.append(",");
            }
        }
        dataString.append(" ) VALUES ( ");
        dataString.append(" '"+Integer.toString(Controller.getUserID())+"', ");
        dataString.append(" '"+Integer.toString(Controller.getPuzzleID())+"', ");
        dataString.append(" '"+Integer.toString(stepID)+"', ");
        dataString.append(" '"+ depthSteps+"', ");
        dataString.append(" '"+ breadthSteps+"', ");
        dataString.append(" '"+DateTime.getTimestamp()+"' ");
        dataString.append(" ) ");

        try{
            //isAdded =(stmt.executeUpdate(dataString.toString())> 0);
            isAdded = (this.updateDataFromServlet(dataString.toString())>0);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return isAdded;
    }
    
    public boolean recordSkillsMatrix(int gridID, int SIGindex, int NSindex, int HSindex, int IRindex, int NPindex, int HPindex){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_SKILLS_MATRIX = DatabaseParam.getTableGameSkillsMatrix();
        final String[] TABLE_GAME_SKILLS_MATRIX_PARAM = DatabaseParam.getTableGameSkillsMatrixParam();
        boolean isRecorded= false;

        dataString.append(" INSERT INTO websudoku.");
        dataString.append(TABLE_GAME_SKILLS_MATRIX);
        dataString.append(" ( ");
        for(int i=0; i<TABLE_GAME_SKILLS_MATRIX_PARAM.length; i++){
            dataString.append(" `");
            dataString.append(TABLE_GAME_SKILLS_MATRIX_PARAM[i].toString());
            dataString.append("` ");
            if((TABLE_GAME_SKILLS_MATRIX_PARAM.length-1)!=i){ /* not last param */
                dataString.append(",");
            }
        }
        dataString.append(" ) VALUES ( ");
        dataString.append(" '"+Integer.toString(Controller.getUserID())+"', ");
        dataString.append(" '"+Integer.toString(Controller.getPuzzleID())+"', ");
        dataString.append(" '"+Integer.toString(gridID)+"', ");
        dataString.append(" '"+Integer.toString(SIGindex)+"', ");
        dataString.append(" '"+Integer.toString(NSindex)+"', ");
        dataString.append(" '"+Integer.toString(HSindex)+"', ");
        dataString.append(" '"+Integer.toString(IRindex)+"', ");
        dataString.append(" '"+Integer.toString(NPindex)+"', ");
        dataString.append(" '"+Integer.toString(HPindex)+"', ");
        dataString.append(" '"+DateTime.getTimestamp()+"' ");
        dataString.append(" ) ");

        try{
            //isRecorded =(stmt.executeUpdate(dataString.toString())> 0);
            this.updateDataFromServlet(dataString.toString());
           // isRecorded = (this.updateDataFromServlet(dataString.toString())>0);
            isRecorded = true;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return isRecorded;
    }
    // 20091029 phrase2 end

    // 20091121 phrase2 start
    public boolean recordElminEntries(int gridID, int ElminValue, int IRindex, int NPindex, int HPindex, int hintsIndex){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_ELMIN_ENTRIES = DatabaseParam.getTableGameElminCellEntries();
        final String[] TABLE_GAME_ELMIN_ENTRIES_PARAM = DatabaseParam.getTableGameElminCellEntriesParam();
        boolean isRecorded= false;

        dataString.append(" INSERT INTO websudoku.");
        dataString.append(TABLE_GAME_ELMIN_ENTRIES);
        dataString.append(" ( ");
        for(int i=0; i<TABLE_GAME_ELMIN_ENTRIES_PARAM.length; i++){
            dataString.append(" `");
            dataString.append(TABLE_GAME_ELMIN_ENTRIES_PARAM[i].toString());
            dataString.append("` ");
            if((TABLE_GAME_ELMIN_ENTRIES_PARAM.length-1)!=i){ /* not last param */
                dataString.append(",");
            }
        }
        dataString.append(" ) VALUES ( ");
        dataString.append(" '"+Integer.toString(Controller.getUserID())+"', ");
        dataString.append(" '"+Integer.toString(Controller.getPuzzleID())+"', ");
        dataString.append(" '"+Integer.toString(gridID)+"', ");
        dataString.append(" '"+Integer.toString(ElminValue)+"', ");
        dataString.append(" '"+Integer.toString(IRindex)+"', ");
        dataString.append(" '"+Integer.toString(NPindex)+"', ");
        dataString.append(" '"+Integer.toString(HPindex)+"', ");
        dataString.append(" '"+Integer.toString(hintsIndex)+"', ");
        dataString.append(" '"+DateTime.getTimestamp()+"' ");
        dataString.append(" ) ");
        
        try{
            //isRecorded =(stmt.executeUpdate(dataString.toString())> 0);
            isRecorded = (this.updateDataFromServlet(dataString.toString())>0);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return isRecorded;
    }
    // 20091121 phrase2 end

    /* Return - void */
    public void recordError(String errorClass, String errorMsg, String timestamp){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_ERRORS = DatabaseParam.getTableGameErrors();
        final String[] TABLE_GAME_ERRRORS_PARAM = DatabaseParam.getTableGameErrorsParam();

        dataString.append(" INSERT INTO websudoku.");
        dataString.append(TABLE_GAME_ERRORS);
        dataString.append(" ( ");
        for(int i=0; i<TABLE_GAME_ERRRORS_PARAM.length; i++){
            dataString.append(" `");
            dataString.append(TABLE_GAME_ERRRORS_PARAM[i].toString());
            dataString.append("` ");
            if((TABLE_GAME_ERRRORS_PARAM.length-1)!=i){ /* not last param */
                dataString.append(",");
            }
        }
        dataString.append(" ) VALUES ( ");
        dataString.append("'"+ Integer.toString(Controller.getUserID()) +"', ");
        dataString.append("'"+ errorClass +"', ");
        dataString.append("'"+ errorMsg +"', ");
        dataString.append("'"+ timestamp +"' ");
        dataString.append(" ) ");


        try{
            /* assume that the errors should insert record successsfully */
            //stmt.executeUpdate(dataString.toString());
            this.updateDataFromServlet(dataString.toString());
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    // 20091019 phrase2 end

    /* Return - ArrayList */
    public Hashtable getGamePuzzles(int min, int max){

        final String TABLE_GAME_PUZZLES = DatabaseParam.getTableGamePuzzles();
        final String[] TABLE_GAME_PUZZLES_PARAM = DatabaseParam.getTableGamePuzzlesParam();

        final String TABLE_GAME_SKILLS_MATRIX = DatabaseParam.getTableGameSkillsMatrix();
        final String[] TABLE_GAME_SKILLS_MATRIX_PARAM = DatabaseParam.getTableGameSkillsMatrixParam();


        Hashtable resultTable = new Hashtable();
        
        /* ',' act as delimeter for this table */
        String delimeter = ",";
        StringBuffer dataString =  new StringBuffer();

        dataString.append("SELECT PuzzleID, NumGivenCells, DefiniteValue1, DefiniteValue2, DefiniteValue3, DefiniteValue4, DefiniteValue5, DefiniteValue6, DefiniteValue7, DefiniteValue8, DefiniteValue9 FROM ");
        dataString.append(TABLE_GAME_PUZZLES);
        dataString.append(" WHERE ");
        dataString.append(" NumGivenCells ");
        dataString.append(" BETWEEN ");
        dataString.append(Integer.toString(min));
        dataString.append(" AND  ");
        dataString.append(Integer.toString(max));
        // 20091112 phrase2 start
        dataString.append(" AND PuzzleID NOT IN ( ");
        dataString.append(" SELECT distinct puzzleID ");
        dataString.append(" FROM ");
        dataString.append(TABLE_GAME_SKILLS_MATRIX);
        dataString.append(" WHERE ");
        dataString.append(" UserID ");
        dataString.append(" = ");
        dataString.append(Controller.getUserID());
        dataString.append(" ) ");
        // 20091112 phrase2 end
        dataString.append(" ORDER BY puzzleID ASC ");

        try {
            //rs = stmt.executeQuery(dataString.toString());
            CachedRowSet rowset = this.selectDataFromServlet(dataString.toString());
            
            /* Given  =  all cells in definite value(s)*/
            int[] GivenCells = null;
            ArrayList<Integer> GivenCellsResult = new ArrayList<Integer> ();

            // to be improve
            //rs.next();
            //while (rs.next()){
            if(rowset.next()){
                for(int i=0; i<TABLE_GAME_PUZZLES_PARAM.length; i++){
                    if (TABLE_GAME_PUZZLES_PARAM[i].startsWith("DefiniteValue")){
                       int[] intResult;
                       String[] strResult = rowset.getString(TABLE_GAME_PUZZLES_PARAM[i]).trim().split(delimeter);
                       intResult = new int[strResult.length];
                       /* set given cells*/
                       for(int j=0; j < strResult.length; j++){
                           intResult[j] = Integer.parseInt(strResult[j]);
                           GivenCellsResult.add(intResult[j]);
                       }
                       resultTable.put(TABLE_GAME_PUZZLES_PARAM[i], intResult);
                    }
                    else{
                       String strResult = rowset.getString(TABLE_GAME_PUZZLES_PARAM[i]);
                       int intResult = Integer.parseInt(strResult);
                       resultTable.put(TABLE_GAME_PUZZLES_PARAM[i], intResult);
                    }
                }
                /* Convert ArrayList to int[]*/
                GivenCells = new int[GivenCellsResult.size()];
                for(int i=0; i<GivenCells.length; i++){
                    GivenCells[i] = (int) GivenCellsResult.get(i);
                }
                /* define the given cells */
                resultTable.put("GivenCells", GivenCells);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultTable;
    }

    // 20091112 phrase2 start
    public int getAutoHintsTimeSlot(int difficultyLevel){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_GAME_AUTO_HINTS_GVING_TIME = DatabaseParam.getTableAutoHintsGivingTime();
        final String[] TABLE_GAME_AUTO_HINTS_GIVING_TIME_PARAM = DatabaseParam.getTableAutoHintsGivingTimeParam();

        dataString.append("SELECT ");
        dataString.append(TABLE_GAME_AUTO_HINTS_GIVING_TIME_PARAM[TABLE_GAME_AUTO_HINTS_GIVING_TIME_PARAM.length - difficultyLevel - 1 ]);
        dataString.append(" FROM ");
        dataString.append(TABLE_GAME_AUTO_HINTS_GVING_TIME);
        dataString.append(" WHERE ");
        dataString.append(TABLE_GAME_AUTO_HINTS_GIVING_TIME_PARAM[0]);
        dataString.append(" = ");
        dataString.append(Controller.getUserID());

        int timeSlot = 0;
        try {

            //rs = stmt.executeQuery(dataString.toString());
            CachedRowSet rowset = this.selectDataFromServlet(dataString.toString());

            /* must only one result */
            if(rowset.next()){
                timeSlot = Integer.parseInt(rowset.getString(TABLE_GAME_AUTO_HINTS_GIVING_TIME_PARAM[TABLE_GAME_AUTO_HINTS_GIVING_TIME_PARAM.length - difficultyLevel - 1 ]));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return timeSlot;

    }
    // 20091112 phrase2 end

    // 20100118
    public void recordMisconception(int value){
        StringBuffer dataString =  new StringBuffer();
        final String TABLE_USER_MISCONCEPTION = DatabaseParam.getTableUserMisconception();
        final String[] TABLE_USER_MISCONCEPTION_PARAM = DatabaseParam.getTableUserMisconceptionParam();

        dataString.append("INSERT INTO websudoku.");
        dataString.append(TABLE_USER_MISCONCEPTION);
        dataString.append(" VALUES ( ");
        dataString.append("'"+ Integer.toString(Controller.getUserID()) +"', ");
        dataString.append("'"+ Integer.toString(Controller.getPuzzleID()) +"', ");
        dataString.append("'"+ Integer.toString(value) +"' ");
        dataString.append(" ) ");

        try{
            //stmt.executeUpdate(dataString.toString());
            this.updateDataFromServlet(dataString.toString());
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

    }


    /* local variables */
    private String className;
    private Connection con;
    private String connectionURL;
    private Statement stmt;
    private ResultSet rs;
    private JApplet webSudoku;

    /* Control Layer -> for expert checkig difficult */
    private ControlLayer Controller;

    /* constant variables */
    /* BasicSudokuParam */
    final static int NUM_CELLS_IN_UNIT = BasicSudokuParam.getNumCellsInUnit();

}
